package com.tac.se2_project2;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.EditText;

public class DatabaseHandler extends SQLiteOpenHelper {
	// Names for category table
	public static final String TABLE_NAME_CATEGORY = "Category";
	public static final String COLUMN_NAME_CAT_NAME = "Name";
	public static final String COLUMN_NAME_CAT_ID = "Id";
	
	// Names for User table
	public static final String TABLE_NAME_USER = "User";
    public static final String COLUMN_NAME_USERID = "UserId";
    public static final String COLUMN_NAME_USERNAME = "Username";
    public static final String COLUMN_NAME_LASTSYNC = "LastSync";

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "contactsManager.db";

	// Contacts table name
	private static final String TABLE_NAME_CONTACT = "Contact";

	// Contacts Table Columns names
	private static final String KEY_ID = "Id";
	private static final String KEY_NAME = "Name";
	private static final String KEY_EMAIL = "Email";
	private static final String KEY_ADDRESS = "Address";
	private static final String KEY_MODIFY = "ModifyDate";
	//private static final String KEY_THEME = "theme";

	private static final String KEY_PH_NO = "PhoneNumber";
	public static final String KEY_ALT_NO = "AltNumber";

	public static final String KEY_COMMENT = "Momment";
	
	public static final String KEY_CAT = "CategoryId";

	public DatabaseHandler(Context context) {
		// Log.d("DBC", "Database Constructor");
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.d("CNT", "Creating New Table");
		String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_NAME_CONTACT + "("
				+ KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_NAME
				+ " TEXT,"

				+ KEY_ADDRESS + " TEXT,"

				+ KEY_PH_NO + " TEXT, " 
				+ KEY_ALT_NO + " TEXT, "

				+ KEY_EMAIL + " TEXT, "

				+ KEY_COMMENT + " TEXT, "
				
				+ KEY_CAT + " INTEGER, "

				+ KEY_MODIFY + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";

		String CREATE_TABLE_CAT = "CREATE TABLE " + TABLE_NAME_CATEGORY + " ("
				+ COLUMN_NAME_CAT_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ COLUMN_NAME_CAT_NAME + " TEXT);";
		
		String CREATE_TABLE_USER = "CREATE TABLE " + TABLE_NAME_USER + " ("
				+ COLUMN_NAME_CAT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ COLUMN_NAME_USERID + " TEXT,"
				+ COLUMN_NAME_USERNAME + " TEXT, "
		        + COLUMN_NAME_LASTSYNC + " DATETIME); ";

		db.execSQL(CREATE_CONTACTS_TABLE);
		db.execSQL(CREATE_TABLE_CAT);
		db.execSQL(CREATE_TABLE_USER);

		db.execSQL("INSERT INTO " + TABLE_NAME_CATEGORY + "(" + COLUMN_NAME_CAT_NAME + ") "
				+ " VALUES (\"Category 1\");");
		db.execSQL("INSERT INTO " + TABLE_NAME_CATEGORY + "(" + COLUMN_NAME_CAT_NAME + ") "
				+ " VALUES (\"Category 2\");");
		db.execSQL("INSERT INTO " + TABLE_NAME_CATEGORY + "(" + COLUMN_NAME_CAT_NAME + ") "
				+ " VALUES (\"Category 3\");");
		db.execSQL("INSERT INTO " + TABLE_NAME_CATEGORY + "(" + COLUMN_NAME_CAT_NAME + ") "
				+ " VALUES (\"Category 4\");");
		db.execSQL("INSERT INTO " + TABLE_NAME_CATEGORY + "(" + COLUMN_NAME_CAT_NAME + ") "
				+ " VALUES (\"Category 5\");");
//		db.close();

	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		Log.d("DOT", "Dropping Old Tables");
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_CONTACT);

		// Create tables again
		onCreate(db);
	}


	// Adding new contact
	public void addContact(Contact contact) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		if(contact.getID() >= 1){
			values.put(KEY_ID, contact.getID());
		}
		if(contact.getModifyDate() != null){
			values.put(KEY_MODIFY, contact.getModifyDate().getTime());
		}
		values.put(KEY_NAME, contact.getName()); // Contact Name
		values.put(KEY_ADDRESS, contact.get_address()); // Contact Name
		values.put(KEY_EMAIL, contact.get_email()); // Contact Name
		
		values.put(KEY_PH_NO, contact.getPhoneNumber());
		values.put(KEY_ALT_NO, contact.get_alt_number());
		   
		values.put(KEY_COMMENT, contact.get_comment());
		
		values.put(KEY_CAT, contact.get_cat());

		// Inserting Row
		db.insert(TABLE_NAME_CONTACT, null, values);
		db.close(); // Closing database connection
	}
	
	public void addCategory(Category cat){
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(COLUMN_NAME_CAT_ID, cat.getID());
		values.put(COLUMN_NAME_CAT_NAME, cat.getName()); 
		db.insert(TABLE_NAME_CATEGORY, null, values);
		db.close(); // Closing database connection
	}

	public void editCategories(String[] x) {
		SQLiteDatabase db = this.getWritableDatabase();

		db.execSQL("UPDATE " + TABLE_NAME_CATEGORY
				+ " SET Name = '" + x[0]
				+ "' WHERE Id = 1");
		db.execSQL("UPDATE " + TABLE_NAME_CATEGORY
				+ " SET Name = '" + x[1]
				+ "' WHERE Id = 2");
		db.execSQL("UPDATE " + TABLE_NAME_CATEGORY
				+ " SET Name = '" + x[2]
				+ "' WHERE Id = 3");
		db.execSQL("UPDATE " + TABLE_NAME_CATEGORY
				+ " SET Name = '" + x[3]
				+ "' WHERE Id = 4");
		db.execSQL("UPDATE " + TABLE_NAME_CATEGORY
				+ " SET Name = '" + x[4]
				+ "' WHERE Id = 5");

		db.close(); // Closing database connection

	}
	
	public String[] getCategoryNames() {
		
		SQLiteDatabase db = this.getReadableDatabase();
		
		String selectQuery = "SELECT * FROM " + TABLE_NAME_CATEGORY + " ORDER BY Id ASC";;
		
		Cursor cursor = db.rawQuery(selectQuery, null);
		
		if (cursor != null) {
			cursor.moveToFirst();
		}
		
		String[] s = new String[5];
		int i = 0;
		do {
			s[i++] = cursor.getString(1);
		} while (cursor.moveToNext());
		
		
		cursor.close();
		db.close();
		
		return s;
		
		
	}
	
	public Category[] retreiveAllCategories() {
		
		SQLiteDatabase db = this.getReadableDatabase();
		
		String selectQuery = "SELECT * FROM " + TABLE_NAME_CATEGORY + " ORDER BY Id ASC";;
		
		Cursor cursor = db.rawQuery(selectQuery, null);
		
		if (cursor != null) {
			cursor.moveToFirst();
		}
		
		Category[] s = new Category[5];
		int i = 0;
		do {
			Category cat = new Category();
			cat.setID(Integer.parseInt(cursor.getString(0)));
			cat.setName(cursor.getString(1));
			s[i++] = cat;
		} while (cursor.moveToNext());
		
		
		cursor.close();
		db.close();
		
		return s;
	}
	

	public Contact getContact(int id) {
		Log.d("WW", "We have arrived");
		SQLiteDatabase db = this.getReadableDatabase();
		Log.d("WW", "We have passed");
		
		Contact contact = null;
		Log.d("WW", "We have pp");
		
		String selectQuery = "SELECT * FROM " + TABLE_NAME_CONTACT
				+ " WHERE Id = '" + id + "'";
		
		Cursor cursor = db.rawQuery(selectQuery, null);
		
		Log.d("WW", "We have pp");
		if (cursor != null) {
			cursor.moveToFirst();

		contact = new Contact();
					
		int u = Integer.parseInt(cursor.getString(0));
		Log.d("t0", u + "");
		contact.setID(u);
		
		String t = cursor.getString(1);
		Log.d("t1", t);
		contact.setName(t);
		
		t = cursor.getString(2);
		Log.d("t2", t);
		contact.set_address(t);
				
		t = cursor.getString(3);
		Log.d("t3", t);
		contact.setPhoneNumber(t);
		
		t = cursor.getString(4);
		Log.d("t4", t);
		contact.set_alt_number(t);
		
		t = cursor.getString(5);
		Log.d("t5", t);
		contact.set_email(t);
		
		t = cursor.getString(6);
		Log.d("t6", t);
		contact.set_comment(t);
		
		t = cursor.getString(7);
		Log.d("t7", t);
		contact.set_cat(Integer.parseInt(t));
		
		}
		
		cursor.close();
		db.close();
		
		// return contact
		return contact;
	}

	public ArrayList<Contact> getAllContacts() {
		ArrayList<Contact> contactList = new ArrayList<Contact>();
		// Select All Query
		String selectQuery = "SELECT * FROM " + TABLE_NAME_CONTACT
				+ " ORDER BY Name DESC";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Contact contact = new Contact();
				contact.setID(Integer.parseInt(cursor.getString(0)));
				contact.setName(cursor.getString(1));
				contact.set_address(cursor.getString(2));
				contact.setPhoneNumber(cursor.getString(3));
				contact.set_alt_number(cursor.getString(4));
				contact.set_email(cursor.getString(5));
				contact.set_comment(cursor.getString(6));
				contact.set_cat(Integer.parseInt(cursor.getString(7)));
				contact.setModifyDate(new java.sql.Date(cursor.getLong(8)));
				// Adding contact to list
				contactList.add(contact);
			} while (cursor.moveToNext());
		}

		cursor.close();

		db.close();


		// return contact list
		return contactList;
	}

	public ArrayList<Contact> getSearchContacts(String search) {
		Log.d("GAC", "Get all contacts");
		ArrayList<Contact> contactList = new ArrayList<Contact>();
		// Select All Query
		Log.d("SS", "SELECT * FROM " + TABLE_NAME_CONTACT + " WHERE Name LIKE '"
				+ search + "' OR " + KEY_PH_NO + " LIKE '" + search + "' ORDER BY Name DESC");
		String selectQuery = "SELECT * FROM " + TABLE_NAME_CONTACT + " WHERE Name LIKE '%"
				+ search + "%' OR " + KEY_PH_NO + " LIKE '%" + search + "%' ORDER BY Name DESC";

		Log.d("GWDB", "Get writeable");
		SQLiteDatabase db = this.getWritableDatabase();
		Log.d("NC", "New cursor");
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Contact contact = new Contact();
				contact.setID(Integer.parseInt(cursor.getString(0)));
				contact.setName(cursor.getString(1));
				contact.set_address(cursor.getString(2));
				contact.setPhoneNumber(cursor.getString(3));
				contact.set_alt_number(cursor.getString(4));
				contact.set_email(cursor.getString(5));
				contact.set_comment(cursor.getString(6));
				// Adding contact to list
				contactList.add(contact);
			} while (cursor.moveToNext());
		}

		cursor.close();

		db.close();

		Log.d("RCL", "Return contact list = " + contactList.size());

		// return contact list
		return contactList;
	}
	
	public ArrayList<Contact> getCatContacts(int cat) {
		Log.d("GAC", "Get all contacts");
		ArrayList<Contact> contactList = new ArrayList<Contact>();
		// Select All Query
		Log.d("SS", "SELECT * FROM " + TABLE_NAME_CONTACT + " WHERE " + KEY_CAT + " = "
				+ cat + " ORDER BY name DESC");
		String selectQuery = "SELECT * FROM " + TABLE_NAME_CONTACT + " WHERE " + KEY_CAT + " = "
				+ cat + " ORDER BY Name DESC";

		Log.d("GWDB", "Get writeable");
		SQLiteDatabase db = this.getWritableDatabase();
		Log.d("NC", "New cursor");
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Contact contact = new Contact();
				contact.setID(Integer.parseInt(cursor.getString(0)));
				contact.setName(cursor.getString(1));
				contact.set_address(cursor.getString(2));
				contact.setPhoneNumber(cursor.getString(3));
				contact.set_alt_number(cursor.getString(4));
				contact.set_email(cursor.getString(5));
				contact.set_comment(cursor.getString(6));
				// Adding contact to list
				contactList.add(contact);
			} while (cursor.moveToNext());
		}

		cursor.close();

		db.close();

		Log.d("RCL", "Return contact list = " + contactList.size());

		// return contact list
		return contactList;
	}
	
	public int getContactsCount() {
		String countQuery = "SELECT  * FROM " + TABLE_NAME_CONTACT;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		cursor.close();
		db.close();

		// return count
		return cursor.getCount();
	}

	
	
	public int updateContact(Contact contact) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_NAME, contact.getName());
		
		values.put(KEY_PH_NO, contact.getPhoneNumber());
		values.put(KEY_ALT_NO, contact.get_alt_number());
		
		values.put(KEY_ADDRESS, contact.get_address());
		values.put(KEY_CAT, contact.get_cat());
		values.put(KEY_COMMENT, contact.get_comment());
		values.put(KEY_EMAIL, contact.get_email());
		values.put(KEY_MODIFY, new Date().getTime());

		// updating row
		int i = db.update(TABLE_NAME_CONTACT, values, KEY_ID + " = ?",
				new String[] { String.valueOf(contact.getID()) });
		
		db.close();
		
		return i;
	}

	public void deleteContact(Contact contact) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_NAME_CONTACT, KEY_ID + " = ?",
				new String[] { String.valueOf(contact.getID()) });
		db.close();
	}

	// close the database connection
	public void close() {
		SQLiteDatabase db = this.getWritableDatabase();
		if (db != null)
			db.close(); // close the database connection
	} // end method close
	
	int retreiveLatest()
	{
		int toReturn = -1;
		
		new ArrayList<Contact>();
		String selectQuery = "SELECT * FROM " + TABLE_NAME_CONTACT
				+ " ORDER BY Id DESC";


		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {

				toReturn = Integer.parseInt(cursor.getString(0));

		}
		cursor.close();

		db.close();

		// return contact list
		return toReturn;
		
	}
	

	public ArrayList<String> getAllCategories() {
		ArrayList<String> catList = new ArrayList<String>();

		String selectQuery = "SELECT * FROM " + TABLE_NAME_CATEGORY;


		SQLiteDatabase db = this.getWritableDatabase();

		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
		
				String x = cursor.getString(1);

				catList.add(x);
				
				
			} while (cursor.moveToNext());
		}

		cursor.close();

		db.close();

		// return contact list
		return catList;
	}
	
	//User functions
	
	public Integer CheckUserLoggedIn(){
		SQLiteDatabase db = this.getReadableDatabase();
		
		//db.delete(TABLE_NAME_USER, null,null);
		
		Cursor cursor = db.query(TABLE_NAME_USER, new String[] {COLUMN_NAME_USERID, COLUMN_NAME_USERNAME, COLUMN_NAME_LASTSYNC}, null, null, null, null, null);
		cursor.moveToFirst();
		Date nowDate = new Date(System.currentTimeMillis());
		Date checkDate;
		if(cursor.getCount() == 0){
			cursor.close();
			db.close();
			return -1;
		}
		checkDate = new Date(cursor.getLong(cursor.getColumnIndex(COLUMN_NAME_LASTSYNC)));
		Calendar cal = Calendar.getInstance();
		cal.setTime(checkDate);
		cal.add(Calendar.MONTH, 1);
		if(cal.before(nowDate)){
			cursor.close();
			db.close();
			return 0;
		}
		else{
			int userId = cursor.getInt(cursor.getColumnIndex(COLUMN_NAME_USERID));
			cursor.close();
			db.close();
			new Contact();
			return userId;
		}
	}
	
	public void UpdateUserLogInTime(String username){
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		Date newDate = new Date();
		Calendar cal = Calendar.getInstance(); 
		cal.add(Calendar.MONTH, 1);
		newDate.setTime(cal.getTimeInMillis());
		values.put(COLUMN_NAME_LASTSYNC, newDate.getTime());

		db.update(TABLE_NAME_USER, values, COLUMN_NAME_USERNAME + " = ?",
				new String[] { username });
		
		db.close();
	}
	
	public void DeleteAllContacts(){
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_NAME_CONTACT, null, null);
		db.close();
	}
	
	public void DeleteAllCategories(){
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_NAME_CATEGORY, null, null);
		db.close();
	}
	
	public void InsertNewCloudUser(int userId, String username){
		SQLiteDatabase db = this.getWritableDatabase();
		//remove previous user
		db.delete(TABLE_NAME_USER, null,null);
		ContentValues values = new ContentValues();
		values.put(COLUMN_NAME_USERID, userId);
		values.put(COLUMN_NAME_USERNAME, username);
		values.put(COLUMN_NAME_LASTSYNC, new Date().getTime());

		// Inserting Row
		db.insert(TABLE_NAME_USER, null, values);
		db.close(); // Closing database connection
	}
	
	public void LogOutUser(){
		Activity act = new Activity();
		((EditText)act.findViewById(R.id.loggedText)).setText("Not Logged In".toCharArray(),0,"Not Logged In".length());
		SQLiteDatabase db = this.getReadableDatabase();
		db.delete(TABLE_NAME_USER, null,null);
		db.close();
		}
	
	
	
}